﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

using Microsoft.ApplicationBlocks.Data;

using DotNetNuke.Common.Utilities;
using DotNetNuke.Framework.Providers;
using DotNetNuke.Entities.Tabs;
using DotNetNuke.Entities.Modules;

using iFinity.DNN.Modules.CatalookUrlProvider.Entities;

namespace iFinity.DNN.Modules.CatalookUrlProvider.Data
{
    internal class SqlDataProvider : DataProvider 
    {
        private const string ModuleQualifier = "cat_";
        private const string OwnerQualifier = "ifty_";

        #region Private Members
        private const string ProviderType = "data";
        private ProviderConfiguration _providerConfiguration = ProviderConfiguration.GetProviderConfiguration(ProviderType);
        private string _connectionString;
        private string _providerPath;
        private string _objectQualifier;
        private string _databaseOwner;
        #endregion
        #region Constructor
        /// <summary>
        /// Constructs new SqlDataProvider instance
        /// </summary>
        internal SqlDataProvider()
        {
            //Read the configuration specific information for this provider
            Provider objProvider = (Provider)_providerConfiguration.Providers[_providerConfiguration.DefaultProvider];

            //Read the attributes for this provider
            //Get Connection string from web.config
            _connectionString = Config.GetConnectionString();

            if (_connectionString.Length == 0)
            {
                // Use connection string specified in provider
                _connectionString = objProvider.Attributes["connectionString"];
            }

            _providerPath = objProvider.Attributes["providerPath"];

            //override the standard dotNetNuke qualifier with a iFinity one if it exists
            _objectQualifier = objProvider.Attributes["objectQualifier"];
            if ((_objectQualifier != "") && (_objectQualifier.EndsWith("_") == false))
            {
                _objectQualifier += "_";
            }
            else
                if (_objectQualifier == null) _objectQualifier = "";

            _objectQualifier += OwnerQualifier;

            _databaseOwner = objProvider.Attributes["databaseOwner"];
            if ((_databaseOwner != "") && (_databaseOwner.EndsWith(".") == false))
            {
                _databaseOwner += ".";
            }
        }
        #endregion
        #region Properties

        /// <summary>
        /// Gets and sets the connection string
        /// </summary>
        public string ConnectionString
        {
            get { return _connectionString; }
        }
        /// <summary>
        /// Gets and sets the Provider path
        /// </summary>
        public string ProviderPath
        {
            get { return _providerPath; }
        }
        /// <summary>
        /// Gets and sets the Object qualifier
        /// </summary>
        public string ObjectQualifier
        {
            get { return _objectQualifier; }
        }
        /// <summary>
        /// Gets and sets the database ownere
        /// </summary>
        public string DatabaseOwner
        {
            get { return _databaseOwner; }
        }

        #endregion
        #region private members
        /// -----------------------------------------------------------------------------
        /// <summary>
        /// Gets the fully qualified name of the stored procedure
        /// </summary>
        /// <param name="name">The name of the stored procedure</param>
        /// <returns>The fully qualified name</returns>
        /// -----------------------------------------------------------------------------
        private string GetFullyQualifiedName(string name)
        {
            return DatabaseOwner + ObjectQualifier + ModuleQualifier + name;
        }
        #endregion

        #region abstract overridden properties
        internal override void GetCatalookItemsForPortal(int portalId, CatalookUrlOptions clOptions, out FriendlyUrlInfoCol urls)
        {
            urls = new FriendlyUrlInfoCol();

            string sp = GetFullyQualifiedName("GetStoreUrls");
            SqlParameter[] parms = new SqlParameter[4];
            parms[0] = new SqlParameter("@portalId", portalId);
            parms[1] = new SqlParameter("@startingProductId", clOptions.StartingProductId);
            parms[2] = new SqlParameter("@useMetaTitles", clOptions.UseProductMetaTitles);
            parms[3] = new SqlParameter("@primaryCultureCode", clOptions.PrimaryCultureCode);
            SqlDataReader rdr = SqlHelper.ExecuteReader(_connectionString, CommandType.StoredProcedure, sp, parms);

            //work out which url fragment to use
            string productFragmentName = "";
            string categoryFragmentName = "";
            string advCatFragmentName = "";
            GetItemColumns(clOptions, out productFragmentName, out categoryFragmentName, out advCatFragmentName);

            while (rdr.Read())
            {
                FriendlyUrlInfo url = new FriendlyUrlInfo();
                FillFriendlyUrl(url, rdr, productFragmentName, categoryFragmentName, advCatFragmentName);
                urls.Add(url);

            }
            rdr.Close();
            rdr.Dispose();
        }
        internal override FriendlyUrlInfo GetItemUrl(int itemId, string itemType, int portalId, CatalookUrlOptions clOptions)
        {
            FriendlyUrlInfo result = null;
            //do a single lookup
            string sp = GetFullyQualifiedName("GetStoreUrl");
            SqlParameter[] parms = new SqlParameter[5];
            parms[0] = new SqlParameter("@itemId", itemId);
            parms[1] = new SqlParameter("@itemType", itemType);
            parms[2] = new SqlParameter("@portalId", portalId);
            parms[3] = new SqlParameter("@startingProductId", clOptions.StartingProductId);
            parms[4] = new SqlParameter("@primaryCultureCode", clOptions.PrimaryCultureCode);
            SqlDataReader rdr = SqlHelper.ExecuteReader(_connectionString, CommandType.StoredProcedure, sp, parms);

            if (rdr.Read())
            {
                string productFragmentName = "";
                string categoryFragmentName = "";
                string advCatFragmentName = "";
                GetItemColumns(clOptions, out productFragmentName, out categoryFragmentName, out advCatFragmentName);
                result = new FriendlyUrlInfo();
                FillFriendlyUrl(result, rdr, productFragmentName, categoryFragmentName, advCatFragmentName);
            }
            rdr.Close();
            rdr.Dispose();
            return result;
        }
        internal override void GetCategoriesForTabs(int portalID, CatalookUrlOptions clOptions, out List<TabCategory> tabCategories)
        {
            tabCategories = new List<TabCategory>();
            string sp = GetFullyQualifiedName("GetStoreTabCategories");
            SqlParameter[] parms = new SqlParameter[1];
            parms[0] = new SqlParameter("@portalId", portalID);
            SqlDataReader rdr = SqlHelper.ExecuteReader(_connectionString, CommandType.StoredProcedure, sp, parms);
            while (rdr.Read())
            {
                int tabId = (int)rdr["TabId"];
                int categoryid = (int)rdr["AdvCatId"];
                TabCategory tabCat = new TabCategory();
                tabCat.TabId = tabId;
                tabCat.CategoryId = categoryid;
                tabCat.CategoryType = "Advanced";
                tabCategories.Add(tabCat);
            }
            rdr.Close();
            rdr.Dispose();
            
        }
        #endregion

        #region private methods
        private void GetItemColumns(CatalookUrlOptions clOptions,  out string productFragmentName, out string categoryFragmentName, out string advCatFragmentName)
        {
            productFragmentName = "UrlFragment1";
            categoryFragmentName = "UrlFragment1";
            advCatFragmentName = "UrlFragment1";
            if (clOptions != null)
            {
                switch (clOptions.ProductUrlSource)
                {
                    case UrlSource.MetaTitle:
                        productFragmentName = "UrlFragment3";
                        break;
                    case UrlSource.Description:
                        productFragmentName = "UrlFragment2";
                        break;
                    default:
                        productFragmentName = "UrlFragment1";
                        break;
                }
                switch (clOptions.CategoryUrlSource)
                {
                    case UrlSource.MetaTitle:
                    case UrlSource.Description:
                        categoryFragmentName = "UrlFragment2"; //no meta title for traditional categories
                        break;
                    default:
                        categoryFragmentName = "UrlFragment1";
                        break;
                }
                switch (clOptions.AdvCatUrlSource)
                {
                    case UrlSource.MetaTitle:
                        advCatFragmentName = "UrlFragment3";
                        break;
                    case UrlSource.Description:
                        advCatFragmentName = "UrlFragment2";
                        break;
                    default:
                        advCatFragmentName = "UrlFragment1";
                        break;
                }
            }

        }
        private void FillFriendlyUrl(FriendlyUrlInfo url,  SqlDataReader rdr, string productFragmentName, string categoryFragmentName, string advCatFragmentName)
        {
            if (!Convert.IsDBNull(rdr["ItemId"]))
                url.itemId = (int)rdr["ItemId"];

            if (!Convert.IsDBNull(rdr["ParentId"]))
                url.parentId = (int)rdr["ParentId"];
            else
                url.parentId = -1;

            string itemType = "";
            if (!Convert.IsDBNull(rdr["ItemType"]))
                itemType = (string)rdr["ItemType"];
            url.itemType = itemType;

            //the num is a general purpose field that is context-dependent on the item Type
            url.urlNum = (int)rdr["UrlNum"];

            url.cultureCode = GetSafeString(rdr, "CultureCode");

            //get the 3 different types of url fragment
            switch (itemType.ToLower())
            {
                case "product":
                    string fragment = GetSafeString(rdr, productFragmentName);
                    if (fragment == "")//fallback if empty
                        fragment = GetSafeString(rdr, "UrlFragment1");
                    url.urlFragment = fragment;
                    break;
                case "category":
                    string catfragment = GetSafeString(rdr, categoryFragmentName);
                    if (catfragment == "")//fallback if empty
                        catfragment = GetSafeString(rdr, "UrlFragment1");
                    url.urlFragment = catfragment;
                    break;
                case "advcategory":
                    string advCatFragment = GetSafeString(rdr, advCatFragmentName);
                    if (advCatFragment == "")//fallback if empty
                        advCatFragment = GetSafeString(rdr, "UrlFragment1");
                    url.urlFragment = advCatFragment;
                    break;
            }

        }
        private string GetSafeString(SqlDataReader rdr, string colName)
        {
            string result = "";
            if (colName != null && colName != "" && !Convert.IsDBNull(rdr[colName]))
                result = (string)rdr[colName];
            return result;
        }

        #endregion

    }
}
